﻿using MiniExcelLibs;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Synergy.特种报表
{
    public partial class Form人员报工数 : Form
    {
        public Form人员报工数()
        {
            InitializeComponent();
        }

        private void Form人员报工数_Load(object sender, EventArgs e)
        {
            comboBox1.SelectedIndex = 0;
            string sql = $@"select '请选择'  Description
                        union all
                        select Description from sfc_operation ";

            DataTable dt = SqlSugarHelp.scope.Ado.GetDataTable(sql);
            comboBox工序.DataSource = dt;
            comboBox工序.DisplayMember = "Description";
            comboBox工序.ValueMember = "Description";
            comboBox工序.SelectedIndex = 0;

            string sql权限 = $@"SELECT [id]      ,[权限编号]      ,[授权人员]  FROM [Z_usergn] where 授权人员 = '{pub.LoginUser}' and 权限编号 = '012'";
            DataTable dt权限 = SqlSugarHelp.scope.Ado.GetDataTable(sql权限);
            if (dt权限 == null || dt权限.Rows.Count <= 0)
            {
                string sql人员 = $@"
                        select cUser_Name from Z_UA_User where cUser_Id = '{pub.LoginUser}' order by cUser_Name";

                DataTable dt人员 = SqlSugarHelp.scope.Ado.GetDataTable(sql人员);
                comboBox报工人.DataSource = dt人员;
                comboBox报工人.DisplayMember = "cUser_Name";
                comboBox报工人.ValueMember = "cUser_Name";
                comboBox报工人.SelectedIndex = 0;
            }
            else
            {
                string sql人员 = $@"
                        select cUser_Name from Z_UA_User order by cUser_Name";

                DataTable dt人员 = SqlSugarHelp.scope.Ado.GetDataTable(sql人员);
                //第一行插入请选择
                DataRow dr = dt人员.NewRow();
                dr["cUser_Name"] = "请选择";
                dt人员.Rows.InsertAt(dr, 0);


                comboBox报工人.DataSource = dt人员;
                comboBox报工人.DisplayMember = "cUser_Name";
                comboBox报工人.ValueMember = "cUser_Name";
                comboBox报工人.SelectedIndex = 0;
            }

           

        }

        private void button刷新_Click(object sender, EventArgs e)
        {
            if (comboBox1.Text == "汇总")
            {
                汇总查询();
            }
            else
            {
                明细查询();
            }
        }

        private void 汇总查询()
        {
            string sql = $@"select  convert(varchar(100),ROW_NUMBER() OVER(ORDER BY z_baogong.工序编号 ASC)) AS 序号,
                            工序编号 工序代码,工序名称,报工人员 报工工号, Z_UA_User.cUser_Name 报工人员,
                            CAST(sum(报工数量) as int)  报工总数量, CAST(sum(报工数量*工序单价) as decimal(18,2)) 报工总金额
                            from z_baogong
                            left join Z_UA_User on Z_UA_User.cUser_Id = z_baogong.报工人员
                            where 1=1";

            if (this.textBox1.Text != "")
            {
                sql += " and z_baogong.物料编号 like '%" + this.textBox1.Text + "%'";
            }
            if (this.comboBox工序.Text != "请选择")
            {
                sql += " and z_baogong.工序名称 = '" + this.comboBox工序.Text + "'";
            }
            if (this.comboBox报工人.Text != "请选择")
            {
                sql += " and Z_UA_User.cUser_Name = '" + this.comboBox报工人.Text + "'";
            }
            if (this.textBox3.Text != "")
            {
                sql += " and z_baogong.生产订单号 like '%" + this.textBox3.Text + "%'";
            }

            if (this.dateTimePicker_ksrq.Checked)
            {
                sql += " and z_baogong.报工时间>='" + this.dateTimePicker_ksrq.Value.ToString("yyyy-MM-dd") + "'";
            }
            if (this.dateTimePicker_jsrq.Checked)
            {
                sql += " and z_baogong.报工时间<'" + this.dateTimePicker_jsrq.Value.AddDays(1).ToString("yyyy-MM-dd") + "'";
            }

            sql += @"group by 工序编号,工序名称,报工人员, Z_UA_User.cUser_Name
                     order by 工序编号,报工总数量 desc";

            DataTable dt = SqlSugarHelp.scope.Ado.GetDataTable(sql);
            //数量 金额汇总
            DataRow dr = dt.NewRow();
            dr["序号"] = "汇总";
            dr["报工总数量"] = dt.Compute("sum(报工总数量)", "");
            dr["报工总金额"] = dt.Compute("sum(报工总金额)", "");
            dt.Rows.Add(dr);

            dataGridView1.Columns.Clear();
            dataGridView1.DataSource = dt;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

        }

        private void 明细查询()
        {
            string sql = $@"select  convert(varchar(100),ROW_NUMBER() OVER(ORDER BY z_baogong.报工时间 ASC)) AS 序号,
                            z_baogong.生产订单号 ,z_baogong.生产订单行号 ,
                            加工顺序,工序名称,
                            z_baogong.物料编号 物料编号,Inventory.cInvName 物料名称,Inventory.cInvStd 物料规格,
                           CAST( sum(z_baogong.报工数量) as int) 报工数量,CAST(z_baogong.工序单价 as decimal(18,2)) 工序单价,
                            CAST(sum(z_baogong.报工数量)*z_baogong.工序单价 as decimal(18,2)) 报工金额,
                            报工人员 报工工号, Z_UA_User.cUser_Name 报工人员,
                            z_baogong.报工时间 报工时间

                            from z_baogong
                            left join Z_UA_User on Z_UA_User.cUser_Id = z_baogong.报工人员
                            left join Inventory on Inventory.cInvCode = z_baogong.物料编号
                            where 1=1";

            if (this.textBox1.Text != "")
            {
                sql += " and z_baogong.物料编号 like '%" + this.textBox1.Text + "%'";
            }
            if (this.comboBox工序.Text != "请选择")
            {
                sql += " and z_baogong.工序名称 = '" + this.comboBox工序.Text + "'";
            }
            if (this.comboBox报工人.Text != "请选择")
            {
                sql += " and Z_UA_User.cUser_Name = '" + this.comboBox报工人.Text + "'";
            }
            if (this.textBox3.Text != "")
            {
                sql += " and z_baogong.生产订单号 like '%" + this.textBox3.Text + "%'";
            }

            if (this.dateTimePicker_ksrq.Checked)
            {
                sql += " and z_baogong.报工时间>='" + this.dateTimePicker_ksrq.Value.ToString("yyyy-MM-dd") + "'";
            }
            if (this.dateTimePicker_jsrq.Checked)
            {
                sql += " and z_baogong.报工时间<'" + this.dateTimePicker_jsrq.Value.AddDays(1).ToString("yyyy-MM-dd") + "'";
            }

            sql += @"group by z_baogong.生产订单号 ,z_baogong.生产订单行号 ,
                    加工顺序,工序名称,
                    z_baogong.物料编号,Inventory.cInvName ,
                    报工人员, Z_UA_User.cUser_Name,
                    z_baogong.报工时间 ,z_baogong.工序单价,Inventory.cInvStd
                        order by 报工时间";
            DataTable dt = SqlSugarHelp.scope.Ado.GetDataTable(sql);
            //数量 金额汇总
            DataRow dr = dt.NewRow();
            dr["序号"] = "汇总";
            dr["报工数量"] = dt.Compute("sum(报工数量)", "");
            dr["报工金额"] = dt.Compute("sum(报工金额)", "");
            dt.Rows.Add(dr);
            dataGridView1.Columns.Clear();
            dataGridView1.DataSource = dt;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox1.Text == "汇总")
            {
                汇总查询();
            }
            else
            {
                明细查询();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //导出
            if (dataGridView1.Rows.Count == 0)
            {
                MessageBox.Show("没有数据可导出");
                return;
            }
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel文件(*.xlsx)|*.xlsx";
            saveFileDialog.FileName = $"人员报工数{DateTime.Now.ToString("yyMMddHHmmss")}";
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                MiniExcel.SaveAs(saveFileDialog.FileName, dataGridView1.DataSource, overwriteFile: true);
                MessageBox.Show("导出成功");
            }
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }
    }
}
